﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Web.Configuration;
using System.Web.Security;
using System.Data;
using System.Data.SqlClient;

public static class DBGfcUsers
{

    #region Members

    private static Configuration config =
                WebConfigurationManager.OpenWebConfiguration("~/");
    private static string connStr = 
        config.ConnectionStrings.ConnectionStrings["LocalSqlServer"].ConnectionString;

    private static string sqlFill =
@"
SELECT * 
FROM gfc_users 
{0}
";
    private static string sqlById =
@"
SELECT * 
FROM gfc_users 
where [Id] = '{0}' 
";
    private static string sqlChilds =
@"
SELECT * 
FROM gfc_users 
where [RootId] = '{0}' 
";
    private static string sqlIns =
@"
INSERT INTO gfc_users
([ProviderId], [IdStr], [RootId])
VALUES ({0}, '{1}', {2})
";
    private static string sqlDel =
@" 
DELETE FROM gfc_users 
WHERE ([IdStr] = '{0}') 
";

    #endregion

    public static Exception MakeUnionAccountsStructure(ref gfcUser gfcuser)
    {
        Exception err = null;
        DataTable tbl1 = new DataTable();
        err = GetById(ref tbl1, gfcuser);
        if (err != null || tbl1.Rows.Count<=0)
            return err;
        DataRow r1 = tbl1.Rows[0];
        if (r1["RootId"] != DBNull.Value)
        {
            gfcuser.Guid = (Guid?)r1["Id"];
            gfcuser.gfcProvider = (gfcProviders)r1["ProviderId"];
            // Get root node
            gfcuser.RootAccountId = (Guid)r1["RootId"];
            gfcuser.ChildAccountsId.Clear();
        }
        else
        {
            gfcuser.Guid = (Guid?)r1["Id"];
            gfcuser.gfcProvider = (gfcProviders)r1["ProviderId"];
            // Get child nodes
            DataTable tbl2 = new DataTable();
            err = GetChildsByRootId(ref tbl2, gfcuser.Guid);
            if(err!=null)
                return err;
            gfcuser.RootAccountId = null;
            gfcuser.ChildAccountsId.Clear();
            foreach (DataRow r2 in tbl2.Rows)
                gfcuser.ChildAccountsId.Add((Guid)r2["Id"]);
        }
        return err;
    }
    public static Exception Exist(gfcUser gfcuser, ref bool bExist)
    {
        Exception err = null;
        bExist = false;
        DataTable tbl = new DataTable();
        err = GetById(ref tbl, gfcuser);
        if (err != null)
            return err;
        if (tbl.Rows.Count > 0)
            bExist = true;
        return err;
    }
    public static Exception GetById(ref DataTable tbl, Guid? parGid)
    {
        Exception err = null;
        if (tbl == null) tbl = new DataTable();
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            try
            {
                string sql = string.Format(sqlById, parGid);
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    //cmd.Parameters.AddWithValue("id", parGid);
                    using (SqlDataAdapter a = new SqlDataAdapter(cmd))
                    {
                        a.SelectCommand = cmd;
                        a.Fill(tbl);
                    }
                }
            }
            catch (Exception ex)
            {
                err = ex;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }
        return err;
    }
    public static Exception GetChildsByRootId(ref DataTable tbl, Guid? parRootGid)
    {
        Exception err = null;
        if (tbl == null) tbl = new DataTable();
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            try
            {
                string sql = string.Format(sqlChilds, parRootGid);
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    //cmd.Parameters.AddWithValue("RootId", parRootGid);
                    using (SqlDataAdapter a = new SqlDataAdapter(cmd))
                    {
                        a.SelectCommand = cmd;
                        a.Fill(tbl);
                    }
                }
            }
            catch (Exception ex)
            {
                err = ex;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }
        return err;
    }
    public static Exception GetById(ref DataTable tbl, gfcUser gfcuser)
    {
        string where = string.Format(" where [IdStr] = '{0}' ", gfcuser.Id);
        return Fill(ref tbl, where);
    }
    public static Exception GetById(ref DataTable tbl, gfcProviders provider, string gfcid)
    {
        gfcUser userTmp = new gfcUser(provider, gfcid);
        string where = string.Format(" where [IdStr] = '{0}' ", userTmp.Id);
        return Fill(ref tbl, where);
    }
    public static Exception GetById(ref DataTable tbl, string idStr)
    {
        string where = string.Format(" where [IdStr] = '{0}' ", idStr);
        return Fill(ref tbl, where);
    }
    private static Exception Fill(ref DataTable tbl, string where)
    {
        Exception err = null;
        if (tbl == null) tbl = new DataTable();
        string sql = string.Format(sqlFill, where);
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            try
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    using (SqlDataAdapter a = new SqlDataAdapter(cmd))
                    {
                        a.SelectCommand = cmd;
                        a.Fill(tbl);
                    }
                }
            }
            catch (Exception ex)
            {
                err = ex;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }
        return err;
    }
    public static Exception Insert(gfcProviders providerId, string idStr, Guid? rootId)
    {
        Exception err = null;
        int i = -1;
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            try
            {
                string guidStr = rootId.ToString();
                if (string.IsNullOrEmpty(guidStr))
                    guidStr = "null";
                else
                    guidStr = string.Format("'{0}'", guidStr);
                string sql = string.Format(sqlIns, (int)providerId, idStr, guidStr);
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    //cmd.Parameters.AddWithValue("ProviderId", providerId);
                    //cmd.Parameters.AddWithValue("IdStr", idStr);
                    //cmd.Parameters.AddWithValue("RootId", rootId);
                    i = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                err = ex;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }
        return err;
    }
    public static Exception Delete(string idStr)
    {
        Exception err = null;
        int i = -1;
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            try
            {
                string sql = string.Format(sqlDel, idStr);
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    //cmd.Parameters.AddWithValue("ProviderId", idStr);
                    i = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                err = ex;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }
        return err;
    }

}
